package com;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class ExcelData {
	private static POIFSFileSystem fs;
	private static HSSFWorkbook wb;
	private static HSSFSheet sheet;
	private static HSSFRow row;

	public static String[][] readExcelContent(InputStream is) throws IOException {
		fs = new POIFSFileSystem(is);
		wb = new HSSFWorkbook(fs);
		sheet = wb.getSheetAt(0);
		// 获取总长度
		int rowNum = sheet.getLastRowNum();
		row = sheet.getRow(0);
		int rowColNum = sheet.getPhysicalNumberOfRows();
		String[][] strArray = new String[rowNum][3];
		System.out.println(rowNum + "," + 3);
		for (int i = 1; i < rowNum; i++) {
			String str = "";
			row = sheet.getRow(i);
			for (int j = 0; j < 3; j++) {
				str = "";
				str += getCellFormatValue(row.getCell((short) j)).trim();
				strArray[i][j] = str;
			}
		}
		return strArray;
	}

	public static String getCellFormatValue(HSSFCell cell) {
		String cellValue = "";
		if (cell != null) {
			switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_STRING:
				cellValue = cell.getRichStringCellValue().getString();
				break;

			default:
				cellValue = " ";
				break;
			}
		}
		return cellValue;
	}

	public static void main(String arg[]) throws IOException {
		File file = new File("text5.xls");
		File file1 = new File("ImportData5.txt");
		FileInputStream is = new FileInputStream(file);
		FileOutputStream fos = new FileOutputStream(file1);

		String[][] map = readExcelContent(is);
		int k=1;
		for (int i = 1; i < map.length; i++) {
			for (int j = 0; j < 3; j++) {
				if (map[i][j] == null || map[i][j].equals("")) {
				}else{
					String sql = "insert into categoryDt (categories,catcode,catname,ischild,ENTID) values (";
					StringBuffer sb = new StringBuffer(sql.toString());
					sb.append("'6ih95uyua7lpyznj',");
					sb.append(map[i][j]);
					if (j == 2) {
						sb.append(",'Y'");
					} else {
						sb.append(",'N'");
					}
					sb.append(",'E1VQBVG2U48'");
					sb.append(");");
					sb.append("\r\n");
					System.out.println(i + "," + k + sb.toString());
					byte[] bytes = sb.toString().getBytes();
					fos.write(bytes);
					k++;
				}
			}
		}
		fos.close();
	}
}
